package com.bj58.data.emailreports.service.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import com.bj58.data.emailreports.dao.CommonDao;
import com.bj58.data.emailreports.dao.SrcDbPool.DBType;
import com.bj58.data.emailreports.model.CPCHour;
import com.bj58.data.emailreports.service.CPCOpenHourService;

public class CPCOpenHourServiceImpl implements CPCOpenHourService {

	private CommonDao comDao = new CommonDao();
	
	@Override
	public List<CPCHour> getTotalData(int hour, String nowDate, String lastDate,String lastWeek, String condition) throws Exception {
		StringBuffer sql = new StringBuffer();
		
		List<String> dateList = new ArrayList<String>();
		dateList.add(nowDate);dateList.add(lastDate);
		dateList.add(lastWeek);
		
		List<CPCHour> finalList = new ArrayList<CPCHour>();
		String cols1[]={"statDate","amount","cashAmount","cusCount","ARPU","units","click","ACP","accountHitCus","budgetHitCus","accountWillHitCus","budgetWillHitCus"};
		List<String> colsList=Arrays.asList(cols1);
		
		for (String date : dateList) {
			String month = date.substring(0,date.lastIndexOf("-")).replace("-", "");
			sql.delete(0, sql.length()+1);
			
			sql.append(" select stat_date,sum(cost)/100 as '消费',");
			sql.append("sum(cash)/100 as '现金消费', count(DISTINCT user_id) as '总客户数',");
			sql.append("sum(cost)/count(distinct user_id)/100 as 'ARPU',");
			sql.append("count(DISTINCT subscribe_id) as '推广单元数',");
			sql.append("sum(click) as '总点击',");
			sql.append("sum(cost)/sum(click)/100 as 'ACP',");
			sql.append("count(DISTINCT if(state=2,user_id,null)) as '余额撞线客户数',");
			sql.append("count(DISTINCT if(state=1,user_id,null)) as '预算撞线客户数', ");
			sql.append("count(distinct if(state_yuzhuangxian = 2,user_id,null)) as '余额预撞线客户数', ");
			sql.append("count(distinct if(state_yuzhuangxian = 1,user_id,null)) as '预算预撞线客户数' ");
			sql.append("from data_bi.t_jingzhun_user_click_aduserantispam_hour_"+month);
			sql.append(" where stat_date = '"+date+"' ");
			sql.append(" and stat_hour<="+hour);
			
			finalList.addAll(comDao.getCommonStatList(sql.toString(), colsList, CPCHour.class, DBType.db_131_data_bi.getValue()));
		}
		return finalList;
	}
	@Override
	public List<CPCHour> getHourData(int hour, String nowDate, String lastDate,String lastWeek, String condition) throws Exception {
		StringBuffer sql = new StringBuffer();
		
		List<String> dateList = new ArrayList<String>();
		dateList.add(nowDate);dateList.add(lastDate);
		dateList.add(lastWeek);
		
		List<CPCHour> finalList = new ArrayList<CPCHour>();
		String cols1[]={"statDate","cate","amount","cashAmount","click","cusCount","units","accountHitCus","budgetHitCus","accountWillHitCus","budgetWillHitCus"};
		List<String> colsList=Arrays.asList(cols1);
		
		for (String date : dateList) {
			String month = date.substring(0,date.lastIndexOf("-")).replace("-", "");
			sql.delete(0, sql.length()+1);
			
			sql.append(" select stat_date,stat_hour,sum(cost)/100 as '消费',");
			sql.append("sum(cash)/100 as '现金消费',");
			sql.append("sum(click) as '总点击',");
			sql.append("count(DISTINCT user_id) as '总客户数',");
			sql.append("count(DISTINCT subscribe_id) as '推广单元数',");
			sql.append("count(DISTINCT if(state=2,user_id,null)) as '余额撞线客户数',");
			sql.append("count(DISTINCT if(state=1,user_id,null)) as '预算撞线客户数', ");
			sql.append("count(distinct if(state_yuzhuangxian = 2,user_id,null)) as '余额预撞线客户数', ");
			sql.append("count(distinct if(state_yuzhuangxian = 1,user_id,null)) as '预算预撞线客户数' ");
			sql.append("from data_bi.t_jingzhun_user_click_aduserantispam_hour_"+month);
			sql.append(" where stat_date = '"+date+"' ");
			sql.append(" and stat_hour<="+hour);
			sql.append(" group by stat_hour");
			
			finalList.addAll(comDao.getCommonStatList(sql.toString(), colsList, CPCHour.class, DBType.db_131_data_bi.getValue()));
		}
		return finalList;
	}
	
	@Override
	public List<CPCHour> getCateHourData(int hour, String nowDate,String lastDate, String lastWeek, String condition)throws Exception {
		StringBuffer sql = new StringBuffer();
		
		List<String> dateList = new ArrayList<String>();
		dateList.add(nowDate);dateList.add(lastDate);
		dateList.add(lastWeek);
		
		List<CPCHour> finalList = new ArrayList<CPCHour>();
		String cols1[]={"statDate","hour","cate","amount","cashAmount","click","cusCount","units","accountHitCus","budgetHitCus","accountWillHitCus","budgetWillHitCus"};
		List<String> colsList=Arrays.asList(cols1);
		
		for (String date : dateList) {
			String month = date.substring(0,date.lastIndexOf("-")).replace("-", "");
			sql.delete(0, sql.length()+1);
			
			sql.append(" select stat_date,stat_hour,");
			sql.append(" case  when business_type_biz='huangye'  and disp_cate1_name <>'招商加盟' then '黄页'");
			sql.append(" when disp_cate1_name ='招商加盟'  then '招商加盟'");
			sql.append(" when business_type_biz='zhaopin' then '招聘'");
			sql.append(" when business_type_biz='fangchan' then '房产'");
			sql.append(" when business_type_biz='ershouchuangxin' then '二手创新'");
			sql.append(" when business_type_biz='ershoutuiguang' then '二手推广'");
			sql.append(" when business_type_biz='ershouche' then '二手车'");
			sql.append(" else '其他' end,");
			sql.append("sum(cost)/100 as '消费',");
			sql.append("sum(cash)/100 as '现金消费',");
			sql.append("sum(click) as '总点击',");
			sql.append("count(DISTINCT user_id) as '总客户数',");
			sql.append("count(DISTINCT subscribe_id) as '推广单元数',");
			sql.append("count(DISTINCT if(state=2,user_id,null)) as '余额撞线客户数',");
			sql.append("count(DISTINCT if(state=1,user_id,null)) as '预算撞线客户数', ");
			sql.append("count(distinct if(state_yuzhuangxian = 2,user_id,null)) as '余额预撞线客户数', ");
			sql.append("count(distinct if(state_yuzhuangxian = 1,user_id,null)) as '预算预撞线客户数' ");
			sql.append("from data_bi.t_jingzhun_user_click_aduserantispam_hour_"+month);
			sql.append(" where stat_date = '"+date+"' ");
			sql.append(" and stat_hour<="+hour);
			sql.append(" group by stat_hour,3");
			
			finalList.addAll(comDao.getCommonStatList(sql.toString(), colsList, CPCHour.class, DBType.db_131_data_bi.getValue()));
		}
		return finalList;
	}
	
	
	@Override
	public List<CPCHour> getCateTotalData(int hour, String nowDate,String lastDate, String lastWeek, String condition)
			throws Exception {
		StringBuffer sql = new StringBuffer();
		
		List<String> dateList = new ArrayList<String>();
		dateList.add(nowDate);dateList.add(lastDate);
		dateList.add(lastWeek);
		
		List<CPCHour> finalList = new ArrayList<CPCHour>();
		String cols1[]={"statDate","cate","amount","cashAmount","click","cusCount","units","accountHitCus","budgetHitCus","accountWillHitCus","budgetWillHitCus"};
		List<String> colsList=Arrays.asList(cols1);
		
		for (String date : dateList) {
			String month = date.substring(0,date.lastIndexOf("-")).replace("-", "");
			sql.delete(0, sql.length()+1);
			
			sql.append(" select stat_date,");
			sql.append(" case  when business_type_biz='huangye'  and disp_cate1_name <>'招商加盟' then '黄页'");
			sql.append(" when disp_cate1_name ='招商加盟'  then '招商加盟'");
			sql.append(" when business_type_biz='zhaopin' then '招聘'");
			sql.append(" when business_type_biz='fangchan' then '房产'");
			sql.append(" when business_type_biz='ershouchuangxin' then '二手创新'");
			sql.append(" when business_type_biz='ershoutuiguang' then '二手推广'");
			sql.append(" when business_type_biz='ershouche' then '二手车'");
			sql.append(" else '其他' end,");
			sql.append("sum(cost)/100 as '消费',");
			sql.append("sum(cash)/100 as '现金消费',");
			sql.append("sum(click) as '总点击',");
			sql.append("count(DISTINCT user_id) as '总客户数',");
			sql.append("count(DISTINCT subscribe_id) as '推广单元数',");
			sql.append("count(DISTINCT if(state=2,user_id,null)) as '余额撞线客户数',");
			sql.append("count(DISTINCT if(state=1,user_id,null)) as '预算撞线客户数', ");
			sql.append("count(distinct if(state_yuzhuangxian = 2,user_id,null)) as '余额预撞线客户数', ");
			sql.append("count(distinct if(state_yuzhuangxian = 1,user_id,null)) as '预算预撞线客户数' ");
			sql.append("from data_bi.t_jingzhun_user_click_aduserantispam_hour_"+month);
			sql.append(" where stat_date = '"+date+"' ");
			sql.append(" and stat_hour<="+hour);
			sql.append(" group by 2");
			
			finalList.addAll(comDao.getCommonStatList(sql.toString(), colsList, CPCHour.class, DBType.db_131_data_bi.getValue()));
		}
		return finalList;
	}

}
